package db;
import model.*;

import java.sql.*;
import java.util.ArrayList;

/**
 * DBUser.java
 * 
 */
public class DBUser {
	private  Connection con;
	/** Creates a new instance 
	 * of DBUser */
	public DBUser() {
		con = DBConnection.getInstance().getDBcon();
	}

	//Implements the methods from the interface
	// get all Users
	public ArrayList<User> getAllUsers(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation);
	}

	//get one User with the userId
	public User findUser(String userId, boolean retriveAssociation)
	{   String wClause = "  userId = '" + userId + "'";
	return singleWhere(wClause, retriveAssociation);
	}

	//find one User having the fname
	public User searchUserFname(String attValue, boolean retriveAssociation)
	{
		String wClause = "fname like '%" + attValue + "%'";
		System.out.println("SearchUser " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}
	//find one User having the lname
	public User searchUserLname(String attValue, boolean retriveAssociation)
	{
		String wClause = "lname = '" + attValue + "'";
		System.out.println("SearchUser " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}


	//insert a new User
	public int insertUser(User user)
	{     
		int rc = -1;
		String query="INSERT INTO UserTable(userId, fname, lname, dateOfBirth, height, weight, gender )  " +
		"VALUES('"+
		user.getUserId()			+ "','"  +
		user.getFname()  			+ "','"  +
		user.getLname()				+ "','"  +
		user.getDateOfBirth()		+ "',"  +
		user.getHeight()			+ ","  +
		user.getWeight()			+ ",'"  +
		user.getGender()			+"')";   

		System.out.println("insert : " + query);		  					
		try{ // insert new User
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//end try	
		catch(Exception ex){
			System.out.println("Insert exception in User db: "+ex);
		}
		return(rc);
	}

	public int updateUser(User user)
	{
		User userObj  = user;
		int rc=-1;

		String query="UPDATE UserTable SET "+
		"userId ='"+ userObj.getUserId() 	+"', "+
		"fname ='"+ userObj.getFname()		+"', "+
		"lname ='"+ userObj.getLname() 		+"', "+
		"dateOfBirth ='" + userObj.getDateOfBirth() +"',"+
		"height =" + userObj.getHeight() 	+","+
		"weight =" + userObj.getWeight() 	+","+
		"gender ='" + userObj.getGender() 	+"' "+

		" WHERE userId = '" + userObj.getUserId() + "'";
		System.out.println("Update query:" + query);
		try{ // update User
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}//slut try	
		catch(Exception ex){
			System.out.println("Update exception in User db: "+ex);
		}
		return(rc);
	}


	public int delete(String userId)
	{
		int rc=-1;
		String query="DELETE FROM UserTable WHERE userId = '" +
		userId + "'";
		System.out.println(query);
		try{ // delete from User
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}//slut try	
		catch(Exception ex){
			System.out.println("Delete exception in User db: "+ex);
		}
		return(rc);
	}

	//private methods
	//michWere is used whenever we want to select more than one employee
	private ArrayList<User> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<User> list = new ArrayList<User>();	

		String query =  buildQuery(wClause);

		try{ // read the User from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			int snr=0;
			while( results.next() ){
				User userObj = User.getInstance();
				userObj = buildUser(results);	
				list.add(userObj);	
			}//end while
			// if retrieveAssociation is true, the supervisor and department
			// has to be selected for each object in the list
			stmt.close();
		}//slut try	
		catch(Exception e){
			System.out.println("Query exception - select: "+e);
			e.printStackTrace();
		}
		return list;
	}

	//Singelwhere is used when we only select one User 	
	private User singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		User userObj = User.getInstance();
		userObj.setUserId("");
		String query =  buildQuery(wClause);
		System.out.println(query);
		try{ // read the user from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if( results.next() ){
				userObj = buildUser(results);            
			}

		}//end try	
		catch(Exception e){
			System.out.println("Query exception: "+e);
		}
		return userObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query="SELECT * FROM UserTable";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	//method to build an User object
	private User buildUser(ResultSet results)
	{   User userObj = User.getInstance();
	try{
		userObj.setUserId(results.getString(1));
		userObj.setFname(results.getString(2));
		userObj.setLname(results.getString(3));
		userObj.setDateOfBirth(results.getString(4));
		userObj.setHeight(results.getDouble(5));
		userObj.setWeight(results.getDouble(6));
		userObj.setGender(results.getString(7)); 



	}

	catch(Exception e)
	{
		System.out.println("error in building the User object: " + e);
	}
	return userObj;
	}

}  
